package com.faxsun.helper;

import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileWriter;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.Reader;
import java.text.MessageFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.Date;
import java.util.List;
import java.util.Random;


/**
 * This helper converts a csv text file to sql for live data.<br>
 * format like following(with comma separated column and duoble-quote for column data)
 * <p>
 * 1st line: liveTopic,liveStartDate,liveEndDate,liveCoverImg,liveDesc
 * </p> 
 * for example:
 * <pre>
 * "cesasia","2015-07-10 00:00:00","2015-07-13 23:59:59", "http://image.faxsun.com/xxx.jpg","cesasia"
 * </pre>
 * <p>
 * Starting from 2nd line are live message data: liveMsgTxt,liveMsgUrls 
 * </p> 
 * for example:
 * <pre>
 * "主持人宣布会场开幕中。","http://image.faxsun.com/xxx.jpg|http://image.faxsun.com/xxxb.jpg,"
 * </pre>
 * 
 * Simply run as normal java cmd-line application with arguments: csvTextFilePath,liveAdminId,liveId<br>
 * Please ensure the url in csv text already in upyun and fetch the new liveId by checking current db. <br>
 * 
 * Generated sql are put in /resource/sql/mysql/online-data
 *  
 * @author leima
 *
 */
public class LiveDataToSqlHelper {
        
    private static final String ColumnSeparator = ",";
    
    private static final String UrlSeparator = "\\|";
    
    private static final String ColumnQualifier = "\"";   
    

    
    private static final String NEWLIVEPATTERN = "INSERT INTO `fx_live` (`id`,`topic`,`startTime`,`endTime`,`cover_image`,`description`,`isSelected`) "
                                                 + " VALUES ({0},''{1}'',''{2}'',''{3}'',''{4}'',''{5}'',1);";  
    
    private static final String NEWLIVEMGRPATTERN = "INSERT INTO `fx_livemanager` (`user_id`,`live_id`,`inviter`,`role`,`created`) "
                                                 + " VALUES ({0},{1},0,''ADMIN'',''{2}'');"; 
    
    private static final String NEWLIVEMSGPATTERN = "INSERT INTO `fx_livemsg` (`sender_id`,`channel_id`,`content`,`date`,`comments`,`thumbsup`) "
                                                 + " VALUES ({0},{1},{2},''{3}'',0,0);";
    
    public static void main(String[] args){
        if(args.length != 3){
           System.out.println("please show me the csvFilePath, liveAdminId a new liveId");
           return;
        }
        
        String csvTextFile = args[0];
        Long liveAdminId = Long.parseLong(args[1]);
        Long liveId = Long.parseLong(args[2]);       
        
        Reader fr;
        try {
            //fr = new FileReader(csvTextFile);
            fr = new InputStreamReader(new FileInputStream(new File(csvTextFile)), "UTF-8");
            BufferedReader bufr = new BufferedReader(fr);  
            
            String line = null;
            List<String> lines = new ArrayList<String>();
            while((line = bufr.readLine()) != null){
                 //lines.add(new String(line.getBytes("GB2312"), "UTF-8"));
                 lines.add(line);
            }
            bufr.close();

            String[] tmp1Line = null;
            List<String> sqls = new ArrayList<String>();
            for(int i = 0;i < lines.size();i++){
                 String data = lines.get(i);
                 if(data.isEmpty()){
                    continue;
                 }
                 if(i == 0){
                    tmp1Line = data.split(ColumnSeparator);
                    for(int j = 0;j < tmp1Line.length;j++){
                        tmp1Line[j] = tmp1Line[j].trim();
                    }
                    String topic = tmp1Line[0].substring(1, tmp1Line[0].length() - 1);
                    if(topic.startsWith("\"")){
                       topic = topic.substring(1);
                    }
                    String coverImg = tmp1Line[3].substring(1, tmp1Line[3].length() - 1);
                    String liveSql = MessageFormat.format(NEWLIVEPATTERN, String.valueOf(liveId),
                            topic,
                            tmp1Line[1].substring(1, tmp1Line[1].length() - 1),
                            tmp1Line[2].substring(1, tmp1Line[2].length() - 1),
                            coverImg,
                            tmp1Line[4].substring(1, tmp1Line[4].length() - 1));
                    String liveMgrSql = MessageFormat.format(NEWLIVEMGRPATTERN, String.valueOf(liveAdminId),
                                                                                String.valueOf(liveId),
                                                                                tmp1Line[1].substring(1, tmp1Line[1].length() - 1));
                    sqls.add(liveSql + "\r\n");
                    sqls.add(liveMgrSql + "\r\n");
                 }else{
                     String[] tmp = data.split(ColumnSeparator);
                     String msgTime = getMsgTime(lines.size(), i + 1, tmp1Line[1].trim());
                     String msgText = getMsgContent(tmp[0].trim(), tmp[1].trim());
                     
                     String liveMsgSql = MessageFormat.format(NEWLIVEMSGPATTERN, String.valueOf(liveAdminId),String.valueOf(liveId),msgText,msgTime);
                     sqls.add(liveMsgSql + "\r\n");                     
                 }          
            }
            
            File outfile = new File("sqlForLive.sql");
            if(!outfile.exists()){
                FileWriter fw = new FileWriter(outfile); 
                for(String s : sqls){ 
                    fw.write(s);
                }
                fw.flush();
                fw.close();
                System.out.println("generated sql output file at:" + outfile.getAbsolutePath());
            }else{
                System.out.println("same sql output file exist.please back up it or delete it:" + outfile.getAbsolutePath());
            }
            
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        } catch (IOException e) {
            e.printStackTrace();
        } 
    }
    
    private static String getMsgContent(String text,String urls){
        StringBuilder sb = new StringBuilder();
        if(text.startsWith("\"")){
           text = text.substring(1);
        }
        if(urls.startsWith("\"")){
           urls = urls.substring(1);
        }
        if(text.endsWith("\"")){
           text = text.substring(0,text.length() - 1);
        }
        if(urls.endsWith("\"")){
           urls = urls.substring(0,urls.length() - 1);
        }

        String urlFinal = urls;
        if(!urls.isEmpty()){
           String[] urlTmp = urls.split(UrlSeparator);        
           StringBuilder sbUrl = new StringBuilder();
           for(String s : urlTmp){
               sbUrl.append(ColumnQualifier).append(s).append(ColumnQualifier).append(ColumnSeparator);            
           }
           urlFinal = sbUrl.toString();
           urlFinal = urlFinal.substring(0, urlFinal.length() - 1);
        }
        
        sb.append("'{\"text\":\"" + text + "\"" + ",\"url\":[" + urlFinal + "]}'");
        return sb.toString();
    }
    
    private static String getMsgTime(int totalMsg, int msgIdx, String date){
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
        try {            
            Date d = sdf.parse(date.substring(1,11) + " 09:00:00");
            Calendar c = Calendar.getInstance();
            c.setTime(d);
            
            Random r = new Random();
            int minuteOffset = r.nextInt(3);
            int minutePortion = ((17 - 9) * 60) / totalMsg;// starting 09:00 ending at 17:00
            int minutePivot = msgIdx * minutePortion + minuteOffset;

            Random rS = new Random();
            int secondOffset = rS.nextInt(59);
            secondOffset = secondOffset > 10?secondOffset : 10;
            
            c.add(Calendar.MINUTE, minutePivot);
            c.add(Calendar.SECOND, secondOffset);
            
            return sdf.format(c.getTime());
        } catch (ParseException e) {
            e.printStackTrace();
            return date;
        }
    }
}
